IF OBJECT_ID('PQueryLogsPageData') IS NULL
BEGIN
	EXEC('CREATE PROCEDURE PQueryLogsPageData AS ');
END 
GO 
ALTER PROCEDURE PQueryLogsPageData
    @Order VARCHAR(100),
    @Page INT,
    @Rows INT,
	@Sort VARCHAR(100),
	@BeginDate1 DATETIME = NULL,
	@BeginDate2 DATETIME = NULL,
	@LogType NVARCHAR(400) = NULL,
	@RequestParameter NVARCHAR(400) = NULL,
	@ResponseParameter NVARCHAR(400) = NULL,
	@ServiceIP VARCHAR(100) = NULL,
	@Success INT = NULL,
	@Url VARCHAR(200) = NULL,
	@UserIP VARCHAR(100) = NULL,
	@User_Id INT = NULL,
	@ModifyContent NVARCHAR(200) = NULL,
	@Table VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
	CREATE TABLE #SysLog(
		[Id] [INT] NOT NULL,
		[BeginDate] [datetime] NULL,
		[BrowserType] [nvarchar](200) NULL,
		[ElapsedTime] [int] NULL,
		[EndDate] [datetime] NULL,
		[ExceptionInfo] [nvarchar](max) NULL,
		[LogType] [nvarchar](50) NULL,
		[RequestParameter] [nvarchar](max) NULL,
		[ResponseParameter] [nvarchar](max) NULL,
		[Role_Id] [int] NULL,
		[ServiceIP] [nvarchar](100) NULL,
		[Success] [int] NULL,
		[Url] [nvarchar](4000) NULL,
		[UserIP] [nvarchar](100) NULL,
		[UserName] [nvarchar](4000) NULL,
		[User_Id] [int] NULL,
		[ModifyContent] [nvarchar](max) NULL,
		TableName VARCHAR(100)
	)

	CREATE TABLE #LogType
	(
		Res VARCHAR(50)
	)

	SELECT Res AS TableName INTO #TableSource FROM [dbo].Fn_Split(',',@Table);

	DECLARE @SQL NVARCHAR(MAX) = ''
	DECLARE @Query NVARCHAR(MAX);
	SET @Query = '
	INSERT INTO #SysLog
	(
	Id,
	    BeginDate,
	    BrowserType,
	    ElapsedTime,
	    EndDate,
	    ExceptionInfo,
	    LogType,
	    RequestParameter,
	    ResponseParameter,
	    Role_Id,
	    ServiceIP,
	    Success,
	    Url,
	    UserIP,
	    UserName,
	    User_Id,
	    ModifyContent,
		TableName
	)	
	SELECT  Id,BeginDate,
	    BrowserType,
	    ElapsedTime,
	    EndDate,
	    ExceptionInfo,
	    LogType,
	    RequestParameter,
	    ResponseParameter,
	    Role_Id,
	    ServiceIP,
	    Success,
	    Url,
	    UserIP,
	    UserName,
	    User_Id,
	    ModifyContent,
		''@TableName@''
		FROM @TableName@ S WHERE 1=1 
	'
	IF ISNULL(@LogType,'') > ''
	BEGIN
		INSERT INTO #LogType
		(
		    Res
		)
		SELECT Res FROM [dbo].Fn_Split(',',@LogType);
		SET @Query = @Query + ' AND EXISTS (SELECT 1 FROM #LogType A WHERE A.Res = S.LogType) ';

	END 

	IF @RequestParameter IS NOT NULL
		SET @Query = @Query + ' AND S.RequestParameter LIKE ''%' + @RequestParameter +  '%'' ';

		
	IF @ResponseParameter IS NOT NULL
		SET @Query = @Query + ' AND  S.ResponseParameter LIKE ''%' + @ResponseParameter +  '%'' ';

	IF @ModifyContent IS NOT NULL
		SET @Query = @Query + ' AND  S.ModifyContent LIKE ''%' + @ModifyContent +  '%'' ';

	IF @ServiceIP IS NOT NULL
		SET @Query = @Query + ' AND  S.ServiceIP  = ''' + @ServiceIP +  ''' ';

	IF @Url IS NOT NULL
		SET @Query = @Query + ' AND  S.Url  Like ''%' + @Url +  '%'' ';
		
	IF @UserIP IS NOT NULL
		SET @Query = @Query + ' AND  S.UserIP  = ''' + @UserIP +  ''' ';

	IF @BeginDate1 IS NOT NULL
		SET @Query = @Query + ' AND  S.BeginDate  >= ''' + CAST(@BeginDate1 AS VARCHAR(20)) +  ''' ';

	IF @BeginDate2 IS NOT NULL
		SET @Query = @Query + ' AND  S.BeginDate  < ''' + CAST(@BeginDate2 AS VARCHAR(20)) +  ''' ';
		
	IF @Success IS NOT NULL
		SET @Query = @Query + ' AND  S.Success  = ' +  CAST(@Success AS VARCHAR(10)) + '  ';

	IF @User_Id IS NOT NULL
		SET @Query = @Query + ' AND  S.User_Id  = ' +  CAST(@User_Id AS VARCHAR(10)) + '  ';
		

	DECLARE @TableName VARCHAR(100);
	WHILE EXISTS(SELECT 1 FROM #TableSource)
	BEGIN
		SELECT TOP 1 @TableName = TableName FROM #TableSource
		SET @SQL = @SQL + REPLACE(@Query,'@TableName@',@TableName) + ' ;'
		DELETE FROM #TableSource WHERE TableName = @TableName
	END 

	EXEC(@SQL);
	PRINT @SQL
	DECLARE @Count INT ;
	SELECT @count = COUNT(1) FROM #SysLog

	SELECT
	Id,
    BeginDate,
    BrowserType,
    ElapsedTime,
    EndDate,
    ExceptionInfo,
    LogType,
    RequestParameter,
    ResponseParameter,
    Role_Id,
    ServiceIP,
    Success,
    Url,
    UserIP,
    UserName,
    User_Id,
    ModifyContent,TableName,COUNT(1) OVER(PARTITION BY '') AS Total
	FROM #SysLog
	ORDER BY BeginDate DESC   
	OFFSET (@page -1) * @Rows ROWS
	FETCH NEXT @Rows ROWS ONLY;

	SELECT @Count
END;
GO
